package com.sx.dao.daoImpl;

import com.sx.dao.GoodDao;
import com.sx.pojo.Goods;
import com.sx.pojo.GoodsStock;
import com.sx.utils.DatabaseUtil;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品数据访问对象实现类
 *
 * @author xiaobao
 */
public class GoodDaoImpl implements GoodDao {
    //日志记录器
    private static final Logger logger = LogManager.getLogger(GoodDaoImpl.class);

    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database_name?useSSL=false&serverTimezone=UTC";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";

    /**
     * 获取所有商品列表
     * <p>
     * 此方法从数据库中查询所有商品信息，并将其封装成Goods对象列表返回
     *
     * @return 商品列表，如果数据库中没有商品或发生异常，则返回空列表
     */
    public ArrayList<Goods> getAllGoods() {
        //创建数据库连接
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Goods goods;
        ArrayList<Goods> goodsList = new ArrayList<>();
        try {
            //获取数据库连接
            connection = DatabaseUtil.getConnection();
            if (connection != null) {
                //准备SQL查询
                String sql = "select * from goods";
                statement = connection.prepareStatement(sql);
                //执行查询并获取结果集
                resultSet = statement.executeQuery();
                //遍历结果集，将每条记录转换为Goods对象，并添加到列表中
                while (resultSet.next()) {
                    goods = new Goods();
                    goods.setId(resultSet.getString("id"));
                    goods.setCategoryId(resultSet.getString("categoryId"));
                    goods.setName(resultSet.getString("name"));
                    goods.setPrice(resultSet.getFloat("price"));
                    goods.setDescription(resultSet.getString("description"));
                    goods.setInPrice(resultSet.getFloat("inPrice"));
                    goods.setNumber(resultSet.getInt("number"));
                    goods.setCreateDate(resultSet.getDate("createDate"));
                    goods.setShelfLife(resultSet.getInt("shelfLife"));
                    goods.setSupplier_id(resultSet.getString("supplier_id"));
                    goodsList.add(goods); //将商品对象添加到列表中
                }
            } else {
                System.out.println("连接失败");
            }
        } catch (Exception e) {
            //e.printStackTrace();
            logger.error("获取商品列表失败");
        } finally {
            //关闭资源，释放系统资源
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (Exception e) {
                logger.error("关闭资源异常", e);
            }
        }
        logger.info("查询到商品数量：{}", goodsList.size());
        logger.info("查询到商品信息：{}", goodsList);
        return goodsList;
    }

    /**
     * 检查给定的ID在数据库中是否存在
     * 此方法用于在goods表中检查是否存在具有特定ID的商品
     * 它通过查询数据库中与给定ID匹配的商品来实现
     *
     * @param id 要检查的商品ID
     * @return 如果商品ID存在，则返回true；否则返回false
     */
    @Override
    public boolean checkIdExists(String id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取数据库连接
            connection = DatabaseUtil.getConnection();
            // SQL查询语句，用于检查商品ID是否存在
            String sql = "select * from goods where id = ?";
            // 检查是否成功获取数据库连接
            if (connection != null) {
                // 准备SQL语句
                preparedStatement = connection.prepareStatement(sql);
                // 设置查询参数为传入的ID
                preparedStatement.setString(1, id);
                // 执行查询并获取结果集
                resultSet = preparedStatement.executeQuery();
                // 如果结果集中有数据，说明ID存在，返回true
                if (resultSet.next()) {
                    // 记录日志，说明ID存在
                    logger.info("商品ID存在：{}", id);
                    return true;
                }
            } else {
                // 如果连接失败，记录错误日志
                logger.error("连接失败");
            }
        } catch (SQLException e) {
            // 如果发生SQL异常，抛出运行时异常
            throw new RuntimeException(e);
        } finally {
            //关闭资源，释放系统资源
            try {
                // 关闭数据库连接和预编译语句对象
                if (connection != null) connection.close();
                if (preparedStatement != null) preparedStatement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e) {
                // 如果关闭资源时发生异常，记录错误日志
                logger.error("关闭资源异常", e);
                //throw new RuntimeException(e);
            }
        }

        // 如果没有找到匹配的ID或发生错误，返回false
        return false;
    }

    /**
     * 保存商品信息到数据库
     *
     * @param goods 商品对象，包含要保存的商品信息
     * @return 返回保存结果，true表示成功，false表示失败
     */
    @Override
    public boolean saveGood(Goods goods) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取数据库连接
            connection = DatabaseUtil.getConnection();
            if (connection != null) {
                // SQL插入语句，用于将商品信息保存到数据库
                String sql = "insert into goods(id,categoryId,name,price,description,inPrice,number,createDate,shelfLife,supplier_id) values(?,?,?,?,?,?,?,?,?,?)";
                preparedStatement = connection.prepareStatement(sql);
                // 设置SQL语句中的参数值
                preparedStatement.setString(1, goods.getId());
                preparedStatement.setString(2, goods.getCategoryId());
                preparedStatement.setString(3, goods.getName());
                preparedStatement.setFloat(4, goods.getPrice());
                preparedStatement.setString(5, goods.getDescription());
                preparedStatement.setFloat(6, goods.getInPrice());
                preparedStatement.setInt(7, goods.getNumber());
                preparedStatement.setDate(8, (Date) goods.getCreateDate());
                preparedStatement.setInt(9, goods.getShelfLife());
                preparedStatement.setString(10, goods.getSupplier_id());
                // 执行更新操作，并根据执行结果返回布尔值
                logger.info("执行更新操作，并根据执行结果返回布尔值");
                int row = preparedStatement.executeUpdate();
                return row > 0;
            } else {
                // 如果连接失败，记录错误日志
                logger.error("连接失败");
            }
        } catch (SQLException e) {
            // 捕获SQL异常，记录错误日志，并抛出运行时异常
            logger.error("数据库操作异常", e);
            throw new RuntimeException(e);
        } finally {
            // 关闭资源，确保预编译语句和数据库连接被正确关闭
            try {
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                // 如果关闭资源时发生异常，记录错误日志
                logger.error("关闭资源异常", e);
            }
        }
        logger.info("保存商品信息失败");
        // 如果保存操作失败，返回false
        return false;
    }

    @Override
    public Goods getGoodById(String id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DatabaseUtil.getConnection();
            if (connection != null) {
                String sql = "select * from goods where id=?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, id);
                resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    Goods goods = new Goods();
                    goods.setId(resultSet.getString("id"));
                    goods.setCategoryId(resultSet.getString("categoryId"));
                    goods.setName(resultSet.getString("name"));
                    goods.setPrice(resultSet.getFloat("price"));
                    goods.setDescription(resultSet.getString("description"));
                    goods.setInPrice(resultSet.getFloat("inPrice"));
                    goods.setNumber(resultSet.getInt("number"));
                    goods.setCreateDate(resultSet.getDate("createDate"));
                    goods.setShelfLife(resultSet.getInt("shelfLife"));
                    goods.setSupplier_id(resultSet.getString("supplier_id"));
                    logger.info("查询到商品信息：{}", goods);
                    return goods;

                } else {
                    logger.info("没有找到商品信息");
                    return null;
                }
            } else {
                logger.info("连接失败");
            }
        } catch (SQLException e) {
            logger.error("数据库操作异常", e);
        }
        return null;
    }

    @Override
    public boolean deleteGoodById(String id) {
        String sql = "DELETE FROM goods WHERE id=?";
        Connection connection = null;
        try {
            connection = DatabaseUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            int rowsAffected = preparedStatement.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean updateGood(Goods goods) {
        logger.info("更新商品信息：{}", goods);
        String sql = "UPDATE goods SET categoryId=?, name=?, price=?, description=?, inPrice=?, number=?, createDate=?, shelfLife=?, supplier_id=? WHERE id=?";
        Connection connection = null;
        try {
            connection = DatabaseUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, goods.getCategoryId());
            preparedStatement.setString(2, goods.getName());
            preparedStatement.setFloat(3, goods.getPrice());
            preparedStatement.setString(4, goods.getDescription());
            preparedStatement.setFloat(5, goods.getInPrice());
            preparedStatement.setInt(6, goods.getNumber());
            preparedStatement.setDate(7, (Date) goods.getCreateDate());
            preparedStatement.setInt(8, goods.getShelfLife());
            preparedStatement.setString(9, goods.getSupplier_id());
            preparedStatement.setString(10, goods.getId());
            logger.info("设置SQL语句中的参数值");

            int rowsAffected = preparedStatement.executeUpdate();
            logger.info("执行更新操作，并根据执行结果返回布尔值:{}", rowsAffected);
            return rowsAffected > 0;
        } catch (SQLException e) {
            logger.error("数据库操作异常", e);
            //e.printStackTrace();
            return false;
        }
    }

    @Override
    public ArrayList<GoodsStock> findByName(String name) {
        //创建数据库连接
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        GoodsStock goods;
        ArrayList<GoodsStock> goodsList = new ArrayList<>();
        try {
            //获取数据库连接
            connection = DatabaseUtil.getConnection();
            if (connection != null) {
                //准备SQL查询
                String sql = "SELECT \n" +
                        "    g.id,\n" +
                        "    g.Name,\n" +
                        "    g.`number` AS goods_number,\n" +
                        "    s.`number` AS sale_number,\n" +
                        "    (COALESCE(g.`number`, 0) + COALESCE(s.`number`, 0)) AS total_number\n" +
                        "FROM \n" +
                        "    goods g\n" +
                        "LEFT JOIN \n" +
                        "    salebill s ON g.Id = s.Goods_id where g.name = ?\n";
                statement = connection.prepareStatement(sql);
                statement.setString(1, name);
                //执行查询并获取结果集
                resultSet = statement.executeQuery();
                //遍历结果集，将每条记录转换为Goods对象，并添加到列表中
                while (resultSet.next()) {
                    goods = new GoodsStock();
                    goods.setId(resultSet.getString("id"));
                    goods.setName(resultSet.getString("name"));
                    goods.setNumber(resultSet.getInt("goods_number"));
                    goods.setSaleNumber(resultSet.getInt("sale_number"));
                    goods.setTotalNumber(resultSet.getInt("total_number"));
                    goodsList.add(goods); //将商品对象添加到列表中
                }
            } else {
                System.out.println("连接失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("获取库存列表失败");
        } finally {
            //关闭资源，释放系统资源
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (Exception e) {
                logger.error("关闭资源异常", e);
            }
        }
        logger.info("查询到库存数量：{}", goodsList.size());
        logger.info("查询到库存信息：{}", goodsList);
        return goodsList;
    }

    @Override
    public List<Goods> searchGoods(String categoryId, String numberOrder, String shelfLifeOrder, String priceOrder, String supplierId) {

        List<Goods> goodsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM goods WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (categoryId != null&& !categoryId.isEmpty()) {
            sql.append(" AND categoryid =?");
            params.add(categoryId);
        }

        if (supplierId != null&& !supplierId.isEmpty()) {
            sql.append(" AND supplier_id =?");
            params.add(supplierId);
        }

        // 处理商品数量排序
        if ("asc".equals(numberOrder)) {
            sql.append(" ORDER BY number ASC");
        } else if ("desc".equals(numberOrder)) {
            sql.append(" ORDER BY number DESC");
        }

        // 处理剩余保质期排序
        if ("asc".equals(shelfLifeOrder)) {
            sql.append(" ORDER BY shelflife ASC");
        } else if ("desc".equals(shelfLifeOrder)) {
            sql.append(" ORDER BY shelflife DESC");
        }

        // 处理价格排序
        if ("asc".equals(priceOrder)) {
            sql.append(" ORDER BY price ASC");
        } else if ("desc".equals(priceOrder)) {
            sql.append(" ORDER BY price DESC");
        }

        try (Connection connection = DatabaseUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql.toString())) {
            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(i + 1, params.get(i));
            }
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    Goods good = new Goods();
                    good.setId(resultSet.getString("id"));
                    good.setCategoryId(resultSet.getString("categoryid"));
                    good.setName(resultSet.getString("name"));
                    good.setPrice(resultSet.getFloat("price"));
                    good.setDescription(resultSet.getString("description"));
                    good.setInPrice(resultSet.getFloat("inprice"));
                    good.setNumber(resultSet.getInt("number"));
                    good.setCreateDate(resultSet.getDate("createdate"));
                    good.setShelfLife(resultSet.getInt("shelflife"));
                    good.setSupplier_id(resultSet.getString("supplier_id"));
                    goodsList.add(good);
                }
                logger.info("查询到商品数量：{}", goodsList.size());
            }
        } catch (SQLException e) {
            // 处理异常
            logger.error("查询商品失败", e);
            //e.printStackTrace();
        }
        return goodsList;
    }


}

   /* public List<Goods> searchGoods(Integer categoryId, Float minPrice, Float maxPrice, Integer number, Integer remainingShelfLife, Integer supplierId, String keyword) {
        List<Goods> goodsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM goods WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 根据条件动态添加 SQL 子句和参数
        if (categoryId != null) {
            sql.append(" AND categoryid =?");
            params.add(categoryId);
        }
        if (minPrice != null) {
            sql.append(" AND price >=?");
            params.add(minPrice);
        }
        if (maxPrice != null) {
            sql.append(" AND price <=?");
            params.add(maxPrice);
        }
        if (number != null) {
            sql.append(" AND number >=?");
            params.add(number);
        }
        if (supplierId != null) {
            sql.append(" AND supplier_id =?");
            params.add(supplierId);
        }
        if (keyword != null && !keyword.isEmpty()) {
            sql.append(" AND name LIKE?");
            params.add("%" + keyword + "%");
        }
        if (remainingShelfLife != null) {
            // 计算剩余保质期的 SQL 子句，这里假设 `shelf_life` 是保质期天数，`create_date` 是生产日期
            sql.append(" AND (shelflife - DATEDIFF(CURDATE(), createdate) >=?");
            params.add(remainingShelfLife);
        }

        try (Connection connection = DatabaseUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql.toString())) {
            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(i + 1, params.get(i));
            }
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    Goods good = new Goods();
                    *//*good.setId(resultSet.getString("id"));
                    good.setCategoryId(resultSet.getInt("category_id"));
                    good.setName(resultSet.getString("name"));
                    good.setPrice(resultSet.getFloat("price"));
                    good.setDescription(resultSet.getString("description"));
                    good.setInPrice(resultSet.getFloat("in_price"));
                    good.setNumber(resultSet.getInt("number"));
                    good.setCreateDate(resultSet.getDate("create_date"));
                    good.setShelfLife(resultSet.getInt("shelf_life"));
                    good.setSupplier_id(resultSet.getInt("supplier_id"));*//*
                    good.setId(resultSet.getString("id"));
                    good.setCategoryId(resultSet.getInt("categoryid"));
                    good.setName(resultSet.getString("name"));
                    good.setPrice(resultSet.getFloat("price"));
                    good.setDescription(resultSet.getString("description"));
                    good.setInPrice(resultSet.getFloat("inprice"));
                    good.setNumber(resultSet.getInt("number"));
                    good.setCreateDate(resultSet.getDate("createdate"));
                    good.setShelfLife(resultSet.getInt("shelflife"));
                    good.setSupplier_id(resultSet.getInt("supplier_id"));
                    goodsList.add(good);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsList;
    }*/